﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class ForManageReader
    {
        public SqlDataAdapter SeeAllReader()
        {
            string sql = @"select ReaderID,ReaderName,IDCard,BorrowBook,ReturnBook,Phone,Address from Reader";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        public SqlDataAdapter SearchReader(string Attribute)
        {
            //筛选语句，根据关键字搜索读者信息
            string sql = string.Format(@"select ReaderID,ReaderName,IDCard,BorrowBook,ReturnBook,Phone,Address from Reader 
                                where ReaderID like '%{0}%' or ReaderName like '%{0}%' or IDCard like '%{0}%' 
                                or Address like '%{0}%' or Phone like '%{0}%'", Attribute);

            //向数据适配器，填充数据
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        //查询读者借阅信息
        public int SearchBorrowReader(int ReaderID)
        {
            string sql = string.Format(@"select count(*) from Borrow where ReaderID = {0}", ReaderID);
            int i = Convert.ToInt32(DBHelper.ExecuteScalar(sql));
            return i;
        }
        public int DropReaderByReaderID(int ReaderID)
        {
            string sql = string.Format(@"delete from Borrow where ReaderID = {0} and GiveBackDate is not null;
                                delete from Reader where ReaderID = {0};", ReaderID);
            int i = DBHelper.ExecuteNonQuery(sql);
            return i;
        }
    }
}
